package org.cboard.mondrian;

import org.cboard.dataprovider.DataProvider;
import org.cboard.dataprovider.annotation.DatasourceParameter;
import org.cboard.dataprovider.annotation.ProviderName;
import org.cboard.dataprovider.annotation.QueryParameter;
import org.olap4j.*;
import org.olap4j.metadata.Member;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

/**
 * Mondrian 代理
 * Created by soddygosongguochao on 2017/7/12.
 */
@ProviderName(name = "mondrian")
@Service
public class MondrianDataProvider extends DataProvider {

    @DatasourceParameter(label = "server ip and port", type = DatasourceParameter.Type.Input)
    private String serverIp;
    @DatasourceParameter(label = "server port", type = DatasourceParameter.Type.Input)
    private String serverPort;

    @DatasourceParameter(label = "userName", type = DatasourceParameter.Type.Input)
    private String userName;

    @DatasourceParameter(label = "password", type = DatasourceParameter.Type.Input)
    private String passWord;

    @DatasourceParameter(label = "driverName", type = DatasourceParameter.Type.Input)
    private String jdbcDriver;

    @DatasourceParameter(label = "数据库类型，暂只考虑mysql测试用", type = DatasourceParameter.Type.Input)
    private String jdbcType;

    @QueryParameter(label = "模式文件，建好的schema.xml文件", type = QueryParameter.Type.Input)
    private String schemaFile;

    @QueryParameter(label = "mdx查询语句", type = QueryParameter.Type.Input)
    private String query;


    @Override
    public boolean doAggregationInDataSource() {
        //TODO
        return false;
    }

    @Override
    public String[][] getData() throws Exception {


//        <DataSourceInfo><![CDATA[Provider=mondrian;Jdbc=jdbc:mysql://localhost:3306/alen?user=root&password=admin&useUnicode=true&characterEncoding=utf8;JdbcDrivers=com.mysql.jdbc.Driver]]></DataSourceInfo>
        //TODO
        this.schemaFile = "/Users/soddygosongguochao/Documents/git_fork_work/CBoard/src/main/resources/schema/demo.xml";
        String connectionUrl = "jdbc:mondrian:Jdbc=jdbc:mysql://localhost:3306/foodmart2;JdbcUser=root;JdbcPassord=631108;" +
                "Catalog=" + this.schemaFile + ";";
        Class.forName("mondrian.olap4j.MondrianOlap4jDriver");
        Connection connection = DriverManager.getConnection(connectionUrl);
        OlapConnection olapConnection = connection.unwrap(OlapConnection.class);


        this.query = "SELECT {[平均单价],[总销售额],[数量]} ON COLUMNS," +
                "{([产品类别].[所有产品],[客户性别].[所有性别])} on rows FROM [Sales]";
        OlapStatement statement = olapConnection.createStatement();
        CellSet cellSet = statement.executeOlapQuery(

                " select [Time].[Years].Members ON COLUMNS," + " [Product].[Line].Members ON ROWS " + " from [SteelWheelsSales]");


//        cellSet.getFetchSize();

        List<CellSetAxis> axes = cellSet.getAxes();
        List<Position> cps = axes.get(Axis.COLUMNS.axisOrdinal()).getPositions();
        List<Position> rps = axes.get(Axis.ROWS.axisOrdinal()).getPositions();
        for (Position rposition : rps) {
            for (Member m : rposition.getMembers()) {
                System.out.print("[" + m.getName() + "]\t");
            }
            System.out.print("| ");
            for (Position cposition : cps) {
                for (Member m : cposition.getMembers()) {
                    System.out.print("[" + m.getName() + "] ");
                }
                Cell cell = cellSet.getCell(cposition, rposition);
                String value = cell.getFormattedValue();
                System.out.print(value + "| ");
            }
            System.out.println();

        }
        return new String[0][];

    }


    public static void main(String[] args) {
        System.out.println(">>>>>>>>>>start demo>>>>>>>");

        try {
            String schemaFile = "/Users/soddygosongguochao/Documents/git_fork_work/CBoard/src/main/resources/schema/demo.xml";
//            String connectionUrl = "jdbc:mondrian:Jdbc=jdbc:mysql://localhost:3306/foodmart2;JdbcUser=root;JdbcPassword=631108;" +
//                    "Catalog=" + schemaFile + ";";
            String connectionUrl = "jdbc:mondrian:Jdbc=jdbc:mysql://localhost:3306/saiku" +
                    "?user=root&password=631108;" +
                    "Catalog=" + schemaFile + ";";
            Class.forName("mondrian.olap4j.MondrianOlap4jDriver");
//            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            Connection connection = DriverManager.getConnection(connectionUrl);
            OlapConnection olapConnection = connection.unwrap(OlapConnection.class);


            String query = "SELECT {[平均单价],[总销售额],[数量]} ON COLUMNS," +
                    "{([产品类别].[所有产品],[客户性别].[所有性别])} on rows FROM [Sales]";
            OlapStatement statement = olapConnection.createStatement();
            CellSet cellSet = statement.executeOlapQuery(query);


//            cellSet.getFetchSize();

            List<CellSetAxis> axes = cellSet.getAxes();
            List<Position> cps = axes.get(Axis.COLUMNS.axisOrdinal()).getPositions();
            List<Position> rps = axes.get(Axis.ROWS.axisOrdinal()).getPositions();
            for (Position rposition : rps) {
                for (Member m : rposition.getMembers()) {
                    System.out.print("[" + m.getName() + "]\t");
                }
                System.out.print("| ");
                for (Position cposition : cps) {
                    for (Member m : cposition.getMembers()) {
                        System.out.print("[" + m.getName() + "] ");
                    }
                    Cell cell = cellSet.getCell(cposition, rposition);
                    String value = cell.getFormattedValue();
                    System.out.print(value + "| ");
                }
                System.out.println();

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        System.out.println(">>>>>>>>>>>>>>end demo>>>>>>.");


    }


}
